package dao;

import entity.Excel;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import util.AliPool;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

public class ExcelDao {
    public static void main(String[] args) throws IOException, SQLException {
        ClassLoader classLoader=ExcelDao.class.getClassLoader();
        String filename="222.xlsx";
        InputStream input=ClassLoader.getSystemResourceAsStream(filename);
        XSSFWorkbook sheets=new XSSFWorkbook(input);

        XSSFSheet sheet=sheets.getSheetAt(0);
        //获取行数
        int rows=sheet.getPhysicalNumberOfRows();
        XSSFRow row=sheet.getRow(0);
        int cols=row.getPhysicalNumberOfCells();
        System.out.println("文件多少行"+ rows);
        System.out.println("文件多少列"+ cols);
        String c0=null;
        String c1=null;
        String c2=null;
        for(int i=0;i<rows;i++){
            row=sheet.getRow(i);//获取i行
            c0=row.getCell(0).toString();
            c1=row.getCell(1).toString();//获取某列数据
            c2=row.getCell(2).toString();
            new ExcelDao().add(c0,c1,c2);
        }
    }
    public void add(String a,String b,String c)throws SQLException{
        Connection conn= AliPool.getConn();
        String sql="INSERT into cs VALUES(?,?,?);";
        PreparedStatement pst=conn.prepareStatement(sql);
        pst.setString(1,a);
        pst.setString(2,b);
        pst.setString(3,c);
        int as=pst.executeUpdate();
    }
}
